package com.iplant.aps.serviceimpl.dao.sfc;

import com.iplant.aps.service.mesenum.APSOutSourceType;
import com.iplant.aps.service.mesenum.APSReplaceType;
import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.sfc.SFCBatchBOMTaskBPMItem;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class SFCBatchBOMTaskBPMItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(SFCBatchBOMTaskBPMItemDAO.class);

    private static SFCBatchBOMTaskBPMItemDAO Instance = null;

    private SFCBatchBOMTaskBPMItemDAO() {
        super();
    }

    public static SFCBatchBOMTaskBPMItemDAO getInstance() {
        if (Instance == null)
            Instance = new SFCBatchBOMTaskBPMItemDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, SFCBatchBOMTaskBPMItem wSFCBatchBOMTaskBPMItem,
                      OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wSFCBatchBOMTaskBPMItem == null)
                return 0;

            String wSQL = "";
            if (wSFCBatchBOMTaskBPMItem.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.sfc_batchbomtaskbpmitem(SFCBatchBOMTaskID,PartID," +
                                "PartPointID,BOMID," +
                                "BOMItemID,MaterialID,MaterialNumber,UnitID,Level,Responsibility,SapType," +
                                "ReviewComments,Status,Remark,OperatorID,Operator,OperateTime,Quota,QualityLossBig," +
                                "QualityLossSmall,ShiftID,ReplaceType,OutsourceType,SupplierID,FQTY,EvenExchangeRate," +
                                "MinFQTY,RealDemandNumber) VALUES" +
                                "(:SFCBatchBOMTaskID,:PartID,:PartPointID,:BOMID,:BOMItemID,:MaterialID," +
                                ":MaterialNumber,:UnitID,:Level," +
                                ":Responsibility,:SapType,:ReviewComments,:Status,:Remark,:OperatorID,:Operator," +
                                ":OperateTime,:Quota,:QualityLossBig,:QualityLossSmall,:ShiftID,:ReplaceType," +
                                ":OutsourceType,:SupplierID,:FQTY,:EvenExchangeRate,:MinFQTY,:RealDemandNumber);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.sfc_batchbomtaskbpmitem SET SFCBatchBOMTaskID = " +
                                ":SFCBatchBOMTaskID,PartID=:PartID,PartPointID=:PartPointID,BOMID = :BOMID,BOMItemID " +
                                "= :BOMItemID,MaterialID = :MaterialID," +
                                "MaterialNumber = :MaterialNumber,UnitID = :UnitID,Level = :Level,Responsibility = " +
                                ":Responsibility,SapType = :SapType,ReviewComments = :ReviewComments,Status = " +
                                ":Status,Remark = :Remark,OperatorID = :OperatorID,Operator = :Operator,OperateTime =" +
                                " :OperateTime,Quota = :Quota,QualityLossBig = :QualityLossBig,QualityLossSmall = " +
                                ":QualityLossSmall,ShiftID = :ShiftID,ReplaceType = :ReplaceType,OutsourceType = " +
                                ":OutsourceType,SupplierID = :SupplierID,FQTY=:FQTY," +
                                "EvenExchangeRate=:EvenExchangeRate," +
                                "MinFQTY=:MinFQTY,RealDemandNumber=:RealDemandNumber WHERE ID = :ID;",
                        wInstance.Result);
                Calendar wBaseTime = Calendar.getInstance();
                wBaseTime.set(2010, 0, 1, 0, 0, 0);
                if (wSFCBatchBOMTaskBPMItem.Status == 2 && wSFCBatchBOMTaskBPMItem.OperateTime.compareTo(wBaseTime) < 0) {
                    wSFCBatchBOMTaskBPMItem.OperateTime = Calendar.getInstance();
                    wSFCBatchBOMTaskBPMItem.OperatorID = wLoginUser.ID;
                }
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wSFCBatchBOMTaskBPMItem.ID);
            wParamMap.put("SFCBatchBOMTaskID", wSFCBatchBOMTaskBPMItem.SFCBatchBOMTaskID);
            wParamMap.put("PartID", wSFCBatchBOMTaskBPMItem.PartID);
            wParamMap.put("PartPointID", wSFCBatchBOMTaskBPMItem.PartPointID);
            wParamMap.put("BOMID", wSFCBatchBOMTaskBPMItem.BOMID);
            wParamMap.put("BOMItemID", wSFCBatchBOMTaskBPMItem.BOMItemID);
            wParamMap.put("MaterialID", wSFCBatchBOMTaskBPMItem.MaterialID);
            wParamMap.put("MaterialNumber", wSFCBatchBOMTaskBPMItem.MaterialNumber);
            wParamMap.put("UnitID", wSFCBatchBOMTaskBPMItem.UnitID);
            wParamMap.put("Level", wSFCBatchBOMTaskBPMItem.Level);
            wParamMap.put("Responsibility", wSFCBatchBOMTaskBPMItem.Responsibility);
            wParamMap.put("SapType", wSFCBatchBOMTaskBPMItem.SapType);
            wParamMap.put("ReviewComments", wSFCBatchBOMTaskBPMItem.ReviewComments);
            wParamMap.put("Status", wSFCBatchBOMTaskBPMItem.Status);
            wParamMap.put("Remark", wSFCBatchBOMTaskBPMItem.Remark);
            wParamMap.put("OperatorID", wSFCBatchBOMTaskBPMItem.OperatorID);
            wParamMap.put("Operator", wSFCBatchBOMTaskBPMItem.Operator);
            wParamMap.put("OperateTime", wSFCBatchBOMTaskBPMItem.OperateTime);
            wParamMap.put("Quota", wSFCBatchBOMTaskBPMItem.Quota);
            wParamMap.put("QualityLossBig", wSFCBatchBOMTaskBPMItem.QualityLossBig);
            wParamMap.put("QualityLossSmall", wSFCBatchBOMTaskBPMItem.QualityLossSmall);
            wParamMap.put("ShiftID", wSFCBatchBOMTaskBPMItem.ShiftID);
            wParamMap.put("ReplaceType", wSFCBatchBOMTaskBPMItem.ReplaceType);
            wParamMap.put("OutsourceType", wSFCBatchBOMTaskBPMItem.OutsourceType);
            wParamMap.put("SupplierID", wSFCBatchBOMTaskBPMItem.SupplierID);
            wParamMap.put("FQTY", wSFCBatchBOMTaskBPMItem.FQTY);
            wParamMap.put("EvenExchangeRate", wSFCBatchBOMTaskBPMItem.EvenExchangeRate);
            wParamMap.put("MinFQTY", wSFCBatchBOMTaskBPMItem.MinFQTY);
            wParamMap.put("RealDemandNumber", wSFCBatchBOMTaskBPMItem.RealDemandNumber);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wSFCBatchBOMTaskBPMItem.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wSFCBatchBOMTaskBPMItem.setID(wResult);
            } else {
                wResult = wSFCBatchBOMTaskBPMItem.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<SFCBatchBOMTaskBPMItem> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (SFCBatchBOMTaskBPMItem wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.sfc_batchbomtaskbpmitem WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public SFCBatchBOMTaskBPMItem SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        SFCBatchBOMTaskBPMItem wResult = new SFCBatchBOMTaskBPMItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<SFCBatchBOMTaskBPMItem> wList = SelectList(wLoginUser, wID, -1, -1, -1, -1, -1, "", wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<SFCBatchBOMTaskBPMItem> SelectList(BMSEmployee wLoginUser, int wID, int wSFCBatchBOMTaskID,
                                                   int wBOMID, int wBOMItemID, int wMaterialID, int wStatus,
                                                   String wShiftID, OutResult<Integer> wErrorCode) {
        List<SFCBatchBOMTaskBPMItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.MaterialName,t1.MaterialNo,t2.Name UnitText,t3.Name PartName," +
                            "t3.Code PartCode,t4.Name PartPointName,t4.Code PartPointCode FROM {0}" +
                            ".sfc_batchbomtaskbpmitem t "
                            + " left join {0}.mss_material t1 on t.MaterialID=t1.ID "
                            + " left join {0}.cfg_unit t2 on t.UnitID=t2.ID "
                            + " left join {0}.fpc_part t3 on t.PartID=t3.ID "
                            + " left join {0}.fpc_partpoint t4 on t.PartPointID=t4.ID "
                            + "WHERE  1=1  and ( :wID <= 0 or :wID = t.ID ) and ( " +
                            ":wSFCBatchBOMTaskID <= 0 or :wSFCBatchBOMTaskID = t.SFCBatchBOMTaskID ) and ( :wBOMID <=" +
                            " 0" +
                            " or :wBOMID = t.BOMID ) and ( :wBOMItemID <= 0 or :wBOMItemID = t.BOMItemID ) and ( " +
                            ":wMaterialID <= 0 or :wMaterialID = t.MaterialID ) and ( :wStatus <= 0 or :wStatus = " +
                            "t.Status ) and ( :wShiftID is null or :wShiftID = '''' or :wShiftID = t.ShiftID );",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wSFCBatchBOMTaskID", wSFCBatchBOMTaskID);
            wParamMap.put("wBOMID", wBOMID);
            wParamMap.put("wBOMItemID", wBOMItemID);
            wParamMap.put("wMaterialID", wMaterialID);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wShiftID", wShiftID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                SFCBatchBOMTaskBPMItem wItem = new SFCBatchBOMTaskBPMItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.SFCBatchBOMTaskID = StringUtils.parseInt(wReader.get("SFCBatchBOMTaskID"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));
                wItem.PartCode = StringUtils.parseString(wReader.get("PartCode"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.PartPointCode = StringUtils.parseString(wReader.get("PartPointCode"));
                wItem.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                wItem.BOMID = StringUtils.parseInt(wReader.get("BOMID"));
                wItem.BOMItemID = StringUtils.parseInt(wReader.get("BOMItemID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));
                wItem.Level = StringUtils.parseInt(wReader.get("Level"));
                wItem.Responsibility = StringUtils.parseInt(wReader.get("Responsibility"));
                wItem.SapType = StringUtils.parseInt(wReader.get("SapType"));
                wItem.ReviewComments = StringUtils.parseInt(wReader.get("ReviewComments"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.OperatorID = StringUtils.parseInt(wReader.get("OperatorID"));
                wItem.Operator = APSConstans.GetBMSEmployeeName(wItem.OperatorID);
                wItem.OperateTime = StringUtils.parseCalendar(wReader.get("OperateTime"));
                wItem.Quota = StringUtils.parseDouble(wReader.get("Quota"));
                wItem.QualityLossBig = StringUtils.parseString(wReader.get("QualityLossBig"));
                wItem.QualityLossSmall = StringUtils.parseString(wReader.get("QualityLossSmall"));
                wItem.ShiftID = StringUtils.parseString(wReader.get("ShiftID"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeText = APSReplaceType.getEnumType(wItem.ReplaceType).getLable();
                wItem.OutsourceType = StringUtils.parseInt(wReader.get("OutsourceType"));
                wItem.OutsourceTypeText = APSOutSourceType.getEnumType(wItem.OutsourceType).getLable();
                wItem.SupplierID = StringUtils.parseInt(wReader.get("SupplierID"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.EvenExchangeRate = StringUtils.parseDouble(wReader.get("EvenExchangeRate"));
                wItem.MinFQTY = StringUtils.parseDouble(wReader.get("MinFQTY"));
                wItem.RealDemandNumber = StringUtils.parseDouble(wReader.get("RealDemandNumber"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public List<Integer> GetMaterialIDList(BMSEmployee wLoginUser, String wMaterial, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (StringUtils.isEmpty(wMaterial)) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.mss_material " +
                            "where (MaterialNo like ''%{1}%'' or MaterialName like ''%{1}%'');",
                    wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wMaterialID = StringUtils.parseInt(wReader.get("ID"));
                wResult.add((wMaterialID));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<Integer> SelectTaskIDList(BMSEmployee wLoginUser, int wPartID, int wPartPointID, String wMaterial,
                                          List<Integer> wTIDList, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<Integer> wMaterialIDList = new ArrayList<>();
            if (StringUtils.isNotEmpty(wMaterial))
                wMaterialIDList = GetMaterialIDList(wLoginUser, wMaterial, wErrorCode);
            String wMaterials = "";
            if (wMaterialIDList.size() > 0)
                wMaterials = StringUtils.Join(",", wMaterialIDList);

            String wSQL = StringUtils.Format("SELECT SFCBatchBOMTaskID FROM {0}.sfc_batchbomtaskbpmitem " +
                            "where SFCBatchBOMTaskID in ({1}) and (:PartID<=0 or PartID=:PartID) " +
                            "and (:PartPointID<=0 or PartPointID=:PartPointID) " +
                            "and (:MaterialID=null or :MaterialID='''' or MaterialID in ({2}));",
                    wInstance.Result, StringUtils.Join(",", wTIDList), wMaterials);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("PartID", wPartID);
            wParamMap.put("PartPointID", wPartPointID);
            wParamMap.put("MaterialID", wMaterials);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wSFCBatchBOMTaskID = StringUtils.parseInt(wReader.get("SFCBatchBOMTaskID"));
                wResult.add(wSFCBatchBOMTaskID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
